package com.socool.sinofaith.dao;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.socool.sinofaith.model.SifSubTemplateBean;
import com.socool.sinofaith.model.SifTaskAddressBean;
import com.socool.sinofaith.util.AndroidSQLiteOpenHelper;

public class SifTaskAddressDao {
	private AndroidSQLiteOpenHelper dbHelper;

	public SifTaskAddressDao(Context context) {
		dbHelper = new AndroidSQLiteOpenHelper(context);
	}

	// 插入记录
	public int insert(SifTaskAddressBean sifTaskAddressBean) {
		Log.i("SQLite", "----insert----");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		try {
			db.execSQL(
					"insert into "
							+ SifTaskAddressBean.TABLENAME
							+ " (TaskAddress_ID,Task_ID,Province,City,Address,GPS,ExecNum,ExecRange) values(?, ?, ?, ?, ?, ?, ?, ?)",
					new Object[] { sifTaskAddressBean.getTaskAddress_ID(),
							sifTaskAddressBean.getTask_ID(),
							sifTaskAddressBean.getProvince(),
							sifTaskAddressBean.getCity(),
							sifTaskAddressBean.getAddress(),
							sifTaskAddressBean.getGPS(),
							sifTaskAddressBean.getExecNum(),
							sifTaskAddressBean.getExecRange() });
			db.setTransactionSuccessful();
		} catch (Exception e) {
			return 0;
		} finally {
			db.endTransaction();
		}
		db.close();
		return 1;
	}

	// 删除记录
	public int delete(SifTaskAddressBean sifTaskAddressBean) {
		Log.i("SQLite", "----delete----");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		try {
			db.execSQL("delete from " + SifTaskAddressBean.TABLENAME
					+ " where TaskAddress_ID = ?",
					new Object[] { sifTaskAddressBean.getTaskAddress_ID() });
			db.setTransactionSuccessful();
		} catch (Exception e) {
			return 0;
		} finally {
			db.endTransaction();
		}
		db.close();
		return 1;
	}

	// 根据taskId删除记录
	public int deleteByTaskId(String taskId) {
		Log.i("SQLite", "----delete----");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		try {
			db.execSQL("delete from " + SifTaskAddressBean.TABLENAME
					+ " where Task_ID = ?", new Object[] { taskId });
			db.setTransactionSuccessful();
		} catch (Exception e) {
			return 0;
		} finally {
			db.endTransaction();
		}
		db.close();
		return 1;
	}

	// 更新记录
	public int update(SifTaskAddressBean sifTaskAddressBean) {
		Log.i("SQLite", "----update----");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		try {
			db.execSQL(
					"update "
							+ SifTaskAddressBean.TABLENAME
							+ " set Task_ID=?, Province=?, City=?, Address=?, GPS=?, ExecNum=?, ExecRange=? where TaskAddress_ID=?",
					new Object[] { sifTaskAddressBean.getTask_ID(),
							sifTaskAddressBean.getProvince(),
							sifTaskAddressBean.getCity(),
							sifTaskAddressBean.getAddress(),
							sifTaskAddressBean.getGPS(),
							sifTaskAddressBean.getExecNum(),
							sifTaskAddressBean.getExecRange(),
							sifTaskAddressBean.getTaskAddress_ID() });
			db.setTransactionSuccessful();
		} catch (Exception e) {
			return 0;
		} finally {
			db.endTransaction();
		}
		db.close();
		return 1;
	}

	// 查询记录
	public ArrayList<SifTaskAddressBean> query(String id) {
		Log.i("SQLite", "----query----");
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor;
		SifTaskAddressBean sifTaskAddressBean;
		ArrayList<SifTaskAddressBean> list = new ArrayList<SifTaskAddressBean>();
		// 若fileId为null或""则查询所有记录
		if (id == null || id.equals("")) {
			cursor = db.rawQuery("select * from "
					+ SifTaskAddressBean.TABLENAME, null);
		} else {
			cursor = db.rawQuery("select * from "
					+ SifTaskAddressBean.TABLENAME + " where TaskAddress_ID=?",
					new String[] { id });
		}
		while (cursor.moveToNext()) {
			sifTaskAddressBean = new SifTaskAddressBean();
			sifTaskAddressBean.setTaskAddress_ID(cursor.getString(cursor
					.getColumnIndex("TaskAddress_ID")));
			sifTaskAddressBean.setTask_ID(cursor.getString(cursor
					.getColumnIndex("Task_ID")));
			sifTaskAddressBean.setProvince(cursor.getString(cursor
					.getColumnIndex("Province")));
			sifTaskAddressBean.setCity(cursor.getString(cursor
					.getColumnIndex("City")));
			sifTaskAddressBean.setAddress(cursor.getString(cursor
					.getColumnIndex("Address")));
			sifTaskAddressBean.setGPS(cursor.getString(cursor
					.getColumnIndex("GPS")));
			sifTaskAddressBean.setExecNum(cursor.getString(cursor
					.getColumnIndex("ExecNum")));
			sifTaskAddressBean.setExecRange(cursor.getString(cursor
					.getColumnIndex("ExecRange")));
			Log.i("SQLite", sifTaskAddressBean.toString());
			list.add(sifTaskAddressBean);
		}
		cursor.close();
		db.close();
		if (list.size() == 0) {
			Log.i("SQLite", "****表中无数据****");
		}
		return list;
	}

	// 自定义查询记录
	public ArrayList<SifTaskAddressBean> queryBySql(String sql) {
		Log.i("SQLite", "----queryBySql----");
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor;
		SifTaskAddressBean sifTaskAddressBean;
		ArrayList<SifTaskAddressBean> list = new ArrayList<SifTaskAddressBean>();
		cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			sifTaskAddressBean = new SifTaskAddressBean();
			sifTaskAddressBean.setTaskAddress_ID(cursor.getString(cursor
					.getColumnIndex("TaskAddress_ID")));
			sifTaskAddressBean.setTask_ID(cursor.getString(cursor
					.getColumnIndex("Task_ID")));
			sifTaskAddressBean.setProvince(cursor.getString(cursor
					.getColumnIndex("Province")));
			sifTaskAddressBean.setCity(cursor.getString(cursor
					.getColumnIndex("City")));
			sifTaskAddressBean.setAddress(cursor.getString(cursor
					.getColumnIndex("Address")));
			sifTaskAddressBean.setGPS(cursor.getString(cursor
					.getColumnIndex("GPS")));
			sifTaskAddressBean.setExecNum(cursor.getString(cursor
					.getColumnIndex("ExecNum")));
			sifTaskAddressBean.setExecRange(cursor.getString(cursor
					.getColumnIndex("ExecRange")));
			Log.i("SQLite", sifTaskAddressBean.toString());
			list.add(sifTaskAddressBean);
		}
		cursor.close();
		db.close();
		if (list.size() == 0) {
			Log.i("SQLite", "****表中无数据****");
		}
		return list;
	}
}
